package com.sunyard.layoutit.dao;

import java.io.UnsupportedEncodingException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import com.sunyard.layoutit.common.DatabaseConfig;
import com.sunyard.layoutit.common.DateUtil;
import com.sunyard.layoutit.pojo.RepositoryBoxConfigVo;

public class RepositoryBoxConfigDao {

	public String insert(RepositoryBoxConfigVo vo,Connection conn) throws SQLException{
		QueryRunner runner=new QueryRunner();
		String sql = "INSERT INTO " + DatabaseConfig.TABLE_PREFIX + "LAYOUTIT_RE_BOX_CONFIG(ID_,REV_,NAME_,VAL_,ORDER_NUM_,BOX_ID_) VALUES(?,1,?,?,?,?)" ;
		if(null == vo.getID_()){
			String id = String.valueOf(DatabaseConfig.queryNextId("LAYOUTIT_GE_BYTEARRAY"));
			vo.setID_(id);
		}
		String date = DateUtil.getMDrqzhsti19() ;
		runner.update(conn,sql, vo.getID_(),vo.getNAME_(),vo.getVAL_(),vo.getORDER_NUM_(),vo.getBOX_ID_());
		return vo.getID_();
	}
	
	public String insert(String name,String val,int orderNum,String boxId,Connection conn) throws SQLException{
		RepositoryBoxConfigVo vo = new RepositoryBoxConfigVo() ;
		vo.setBOX_ID_(boxId);
		vo.setNAME_(name);
		vo.setORDER_NUM_(orderNum);
		vo.setVAL_(val);
		return insert(vo,conn);
	}
	
	public List<String> queryTextByBoxId(String boxId) throws SQLException{
		DataSource ds = DatabaseConfig.getDataSource() ;
		String sql = "SELECT A.BYTES_ FROM  " + DatabaseConfig.TABLE_PREFIX + "LAYOUTIT_GE_BYTEARRAY A," 
					+ DatabaseConfig.TABLE_PREFIX + "LAYOUTIT_RE_BOX_CONFIG B WHERE A.ID_=B.VAL_ AND B.BOX_ID_=? ORDER BY ORDER_NUM_" ;
		QueryRunner runner=new QueryRunner(ds);
		List<String> res = runner.query(sql, new ResultSetHandler<List<String>>(){
			public List<String> handle(ResultSet rs) throws SQLException {
				List<String> list = new ArrayList<String>() ;
				while(rs.next()){
					Blob b = rs.getBlob("BYTES_") ;
					byte[] bytes = b.getBytes(1, (int)b.length()) ;
					try {
						String text = new String(bytes,DatabaseConfig.BLOB_CHARSET);
						list.add(text);
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
						throw new RuntimeException("编码异常",e) ;
					}
				}
				return list;
			}
		});
		return res ;
	}
	
	public List<String> queryTextByBoxId(String boxId,Connection conn) throws SQLException{
		String sql = "SELECT A.BYTES_ FROM  " + DatabaseConfig.TABLE_PREFIX + "LAYOUTIT_GE_BYTEARRAY A," 
				+ DatabaseConfig.TABLE_PREFIX + "LAYOUTIT_RE_BOX_CONFIG B WHERE A.ID_=B.VAL_ AND B.BOX_ID_=? ORDER BY ORDER_NUM_" ;
		QueryRunner runner=new QueryRunner();
		List<String> res = runner.query(conn,sql, new ResultSetHandler<List<String>>(){
			public List<String> handle(ResultSet rs) throws SQLException {
				List<String> list = new ArrayList<String>() ;
				while(rs.next()){
					Blob b = rs.getBlob("BYTES_") ;
					byte[] bytes = b.getBytes(1, (int)b.length()) ;
					try {
						String text = new String(bytes,DatabaseConfig.BLOB_CHARSET);
						list.add(text);
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
						throw new RuntimeException("编码异常",e) ;
					}
				}
				return list;
			}
		},boxId);
		return res ;
	}
}
